﻿using IceCS.EMS.Model;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;

namespace IceCS.EMS.DAL.Services
{
    public class EmployeeService
    {

        private EmployeeModel MapFrom(DataRow row)
        {
            return new EmployeeModel
            {
                EmployeeID = Convert.ToInt32(row["EmployeeID"]),
                CreatorId = Convert.ToInt32(row["CreatorId"]),
                CreateTime = Convert.ToDateTime(row["CreateTime"]),
                UpdateId = Convert.ToInt32(row["UpdateId"]),
                UpdateTime = Convert.ToDateTime(row["UpdateTime"]),
                LastName = row["LastName"].ToString(),
                FirstName = row["FirstName"].ToString(),
                Position = row["Position"].ToString(),
                Birthday = Convert.ToDateTime(row["Birthday"]),
                Address = row["Address"].ToString(),
                City = row["City"].ToString(),
                Country = row["Country"].ToString(),
                Remark = row["Remark"].ToString()
            };
        }
        //获取雇员列表
        public IList<EmployeeModel> GetEmployeeModelList()
        {
            IList<EmployeeModel> empList = new List<EmployeeModel>();
            EmployeeModel emp;

            string sql = "select EmployeeID,CreatorId,CreateTime,UpdateId,UpdateTime,LastName,FirstName,Position,Birthday,Address,City,Country,Remark from Employee";

            DataTable table = DBHelper.GetDataTable(sql);
            foreach (DataRow row in table.Rows)
            {
                emp = MapFrom(row);
                empList.Add(emp);
            }
            return empList;
        }

        //删除雇员
        public int DeleteEmployee(int employeeID)
        {
            int result = 0;

            string sql = string.Format("delete from Employee where EmployeeID={0}", employeeID);
            if (DBHelper.ExcuteCommand(sql))
                result = 1;

            return result;
        }

        //判定记录是否存在
        public bool Exist(int employeeID)
        {
            bool result = false;
            string sql = string.Format("select count(*) from Employee where EmployeeID={0}", employeeID);
            object obj = DBHelper.GetSingleData(sql);
            result = Convert.ToInt32(obj) > 0 ? true : false;
            return result;
        }

        //根据ID值查找雇员
        public EmployeeModel SearchById(int employeeID)
        {
            EmployeeModel emp = new EmployeeModel();
            string sql = "select EmployeeID,CreatorId,CreateTime,UpdateId,UpdateTime,LastName,FirstName,Position,Birthday,Address,City,Country,Remark from Employee where EmployeeID=" + employeeID;

            DataTable table = DBHelper.GetDataTable(sql);
            if (table.Rows.Count == 0)
            {
                return null;
            }
            return MapFrom(table.Rows[0]);
        }

        //保存对雇员的修改
        public bool SaveEdit(EmployeeModel employee)
        {
            string sql = $"update Employee SET UpdateId=@UpdateId,UpdateTime=@UpdateTime,LastName=@LastName,FirstName=@FirstName," +
                $"Position=@Position,Birthday=@Birthday,Address=@Address,City=@City,Country=@Country,Remark=@Remark " +
                $"WHERE EmployeeID=@EmployeeID";
            var param = new SqlParameter[]
            {
                new SqlParameter("@EmployeeID",employee.EmployeeID),
                new SqlParameter("@UpdateId",employee.UpdateId),
                new SqlParameter("@UpdateTime",employee.UpdateTime),
                new SqlParameter("@LastName",employee.LastName),
                new SqlParameter("@FirstName",employee.FirstName),
                new SqlParameter("@Position",employee.Position),
                new SqlParameter("@Birthday",employee.Birthday),
                new SqlParameter("@Address",employee.Address),
                new SqlParameter("@City",employee.City),
                new SqlParameter("@Country",employee.Country),
                new SqlParameter("@Remark",employee.Remark)
            };
            return DBHelper.ExcuteCommand(sql, param) > -1;
        }

        //根据表中的ID取值自动生成新Id
        public int GetId()
        {
            int id;

            string sqlstr = string.Format("select max(EmployeeID) from Employee");
            var result = DBHelper.GetSingleData(sqlstr);
            id = Convert.ToInt32(result) + 1;
            return id;
        }

        //保存新员工信息
        public bool SaveAdd(EmployeeModel employee)
        {
            string sql = $"insert into Employee" +
                $"(EmployeeID, CreatorId, CreateTime, UpdateId, UpdateTime, LastName, " +
                $"FirstName, Position, Birthday, Address, City, Country, Remark) " +
                $"Values(@EmployeeID, @CreatorId, @CreateTime, @UpdateId, @UpdateTime, @LastName," +
                $"@FirstName, @Position,@Birthday, @Address, @City, @Country, @Remark)";
            var param = new SqlParameter[]
            {
                new SqlParameter("@EmployeeID",employee.EmployeeID),
                new SqlParameter("@CreatorId",employee.CreatorId),
                new SqlParameter("@CreateTime",employee.CreateTime),
                new SqlParameter("@UpdateId",employee.UpdateId),
                new SqlParameter("@UpdateTime",employee.UpdateTime),
                new SqlParameter("@LastName",employee.LastName),
                new SqlParameter("@FirstName",employee.FirstName),
                new SqlParameter("@Position",employee.Position),
                new SqlParameter("@Birthday",employee.Birthday),
                new SqlParameter("@Address",employee.Address),
                new SqlParameter("@City",employee.City),
                new SqlParameter("@Country",employee.Country),
                new SqlParameter("@Remark",employee.Remark)
            };
            return DBHelper.ExcuteCommand(sql, param) > -1;
        }

    }
}
